package com.cfs.daoimpl.sys;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.util.ExcelUtil;
import com.cfs.po.Exp;
import com.cfs.po.sys.CommonBean;
import com.cfs.po.sys.GenRes;
import com.cfs.po.sys.Org;


/**
 * 组织管理dao
 * @author Zhangjie
 * @since 2015-05-23
 * @version 1.0
 */
public class OrgDao extends Dao {
	
	RowMapper<Org> mapper = new RowMapper<Org>() {

		@Override
		public Org mapRow(ResultSet rs, int arg1) throws SQLException {
			Org org = new Org();

			org.setId(rs.getLong("ID"));
			org.setParentId(rs.getLong("PARENTID"));
			org.setOrgCode(rs.getString("ORGCODE"));
			org.setLevels(rs.getInt("LEVELS"));
			org.setName(rs.getString("NAME"));
			org.setAddress(rs.getString("ADDRESS"));
			org.setState(rs.getInt("STATE"));
			return org;
		}

	};

	/**
	 * One ROW
	 * 
	 * @param org
	 */
	public int add(Org org) {
		String sql = DB_SQL.ORG_DEL_BY_ID;

		int rows = jdbcTemplate.update(sql, org.getParentId(), org.getOrgCode(),
				org.getLevels(), org.getState(), org.getName(),
				org.getAddress(), org.getDescription());

		System.out.println("执行 insert: " + rows);
		
		return rows;
	}

	/**
	 * ROW 批量添加
	 * @param orgs
	 */
	public GenRes add(Org[] orgs) {
		String sql = DB_SQL.ORG_INS;
		GenRes gr = new GenRes();
		List<Object[]> paramList = new ArrayList<Object[]>();
		for (Org org : orgs) {
		    List<Long> names = jdbcTemplate.queryForList("SELECT ID FROM TB_ORG WHERE NAME = ?", new Object[]{org.getName()}, Long.class);
		    if(names.size()!=0){
				gr.setExp(new Exp("此机构名称已存在！"));
				return gr;
			}
			List<Long> codes = jdbcTemplate.queryForList("SELECT ID FROM TB_ORG WHERE ORGCODE = ?", new Object[]{org.getOrgCode()},Long.class);
			if(codes.size()!=0){
				gr.setExp(new Exp("组织编码已存在！"));
				return gr;
			}
			if(null==org.getOrgCode()||0==org.getOrgCode().replaceAll(" ", "").length()){
				gr.setExp(new Exp("组织编码不能为空！"));
				return gr;
			}
			paramList.add(new Object[] { org.getParentId(), org.getOrgCode(),
					org.getLevels(), org.getState(), org.getName(),
					org.getAddress(), org.getDescription() });
		}

		int[] rows = jdbcTemplate.batchUpdate(sql, paramList);

		System.out.println("执行表 Multi-insert: " + rows.length);
		gr.setPint(rows.length);
		return gr;
	}

	/**
	 * 批量更新
	 * 
	 * @param org 
	 */
	public GenRes update(Org[] orgs) {
		GenRes gr = new GenRes();
		String sql = DB_SQL.ORG_UPD_BY_ID;
		if(orgs.length==0){
			gr.setPint(0);
			gr.setExp(new Exp("没有传入要修改的组织"));
			return gr;
		}
		Org modifyOrg = orgs[0];
		long modifyId = modifyOrg.getId();
		String orgSql = "SELECT COUNT(ID) FROM TB_ORG WHERE NAME = ?";
		String shopSql = "SELECT COUNT(ID) FROM TB_POINT WHERE SHORTNAME = ?";
		int levels = modifyOrg.getLevels();
		try{
			if(levels<5){
				int countOrg =  jdbcTemplate.queryForInt(orgSql,modifyOrg.getName());
				if(countOrg>0){
					List<String> actualName = jdbcTemplate.queryForList("SELECT NAME FROM TB_ORG WHERE ID = ?", new Object[]{modifyId},String.class);
				    modifyOrg.setName(actualName.get(0));
					gr.setPstr("此组织名称已存在，除组织名称外，已修改其余信息");
				}
			  
			    int row =jdbcTemplate.update(sql, new Object[]{modifyOrg.getParentId(), modifyOrg.getOrgCode(),
			    		modifyOrg.getLevels(), modifyOrg.getName(),
			    		modifyOrg.getAddress(), modifyOrg.getDescription(), modifyOrg.getId()});
			    gr.setPint(row);
			}else if(levels==5){
				int countOrg =  jdbcTemplate.queryForInt(orgSql,modifyOrg.getName());
				int countShop = jdbcTemplate.queryForInt(shopSql, modifyOrg.getName());
				if(countOrg>0||countShop>0){
					List<String> actualName = jdbcTemplate.queryForList("SELECT NAME FROM TB_ORG WHERE ID = ?", new Object[]{modifyId},String.class);
				    modifyOrg.setName(actualName.get(0));
					gr.setPstr("此组织名称已存在，除组织名称外，已修改其余信息");
				}
				int row = jdbcTemplate.update(sql, new Object[]{modifyOrg.getParentId(), modifyOrg.getOrgCode(),
			    		modifyOrg.getLevels(),modifyOrg.getName(),
			    		modifyOrg.getAddress(), modifyOrg.getDescription(), modifyOrg.getId()});
				int row1 = jdbcTemplate.update("update tb_point set shortname=? where id=?", new Object[]{modifyOrg.getName(),modifyOrg.getId()});
				gr.setPint(row+row1);
			}
			return gr;
        }catch (Exception e) {
			e.printStackTrace();
			gr.setExp(new Exp("更新失败，请重新更新"));
			return gr;
		}

	}

	/**
	 * 批量删除
	 * 
	 * @param id
	 */
	public GenRes delete(long[] ids) {
		GenRes gr = new GenRes();
		try{
			if(ids.length==0){
				gr.setExp(new Exp("没有传入要删除的记录"));
				return gr;
			}
			int pint = 0;
            long id = ids[0];
			int levels = jdbcTemplate.queryForInt("SELECT LEVELS FROM TB_ORG WHERE ID = ?", new Object[]{id});
			if(levels==5){
				int salesCount = jdbcTemplate.queryForInt("SELECT COUNT(*) SALESCOUNT FROM TB_SALESREC WHERE POINTID = ?",new Object[]{id});
				if(salesCount>0){
					gr.setExp(new Exp("门店已上报销量不能删除!"));
					return gr;
				}
				int row = jdbcTemplate.update("DELETE FROM TB_POINT WHERE ID = ?", new Object[]{id});
				pint+=row;
			}
			String sql = DB_SQL.ORG_DEL_BY_ID;

			List<Object[]> paramList = new ArrayList<Object[]>();
			for (long l : ids) {
				paramList.add(new Object[] { l });
			}

			int[] rows = jdbcTemplate.batchUpdate(sql, paramList);
			pint+=rows.length;
			gr.setPint(pint);
			System.out.println("执行表 Multi-delete: " + pint);
			return gr;
			
		}catch (Exception e) {
			e.printStackTrace();
			gr.setExp(new Exp("执行出现异常"));
			return gr;
		}
		
		
		//initOrgs();
	}

	/**
	 * ID 查询
	 * @param id
	 */
	public Org get(Long id) {
		Org org = null;
		try{
			List<Org> list = jdbcTemplate.query(DB_SQL.ORG_QUERY_BY_ID,new Object[]{id}, new RowMapper<Org>(){
				
				@Override
				public Org mapRow(ResultSet rs, int arg1) throws SQLException {
					// TODO Auto-generated method stub
					Org o = new Org();
					o.setId(rs.getLong("ID"));
					o.setParentId(rs.getLong("PARENTID"));
					o.setName(rs.getString("NAME"));
					o.setLevels(rs.getInt("LEVELS"));
					o.setAddress(rs.getString("ADDRESS"));
					o.setDescription(rs.getString("DESCRIPTION"));
					o.setOrgCode(rs.getString("ORGCODE"));
					o.setState(rs.getInt("STATE"));
					return o;
				}
			});
			return list.size()==0?org:list.get(0);
		}catch(Exception e){
			System.out.println("id:" + id );
			Exp exp = new Exp("机构数据有误!");
			org.setExp(exp);
		}
		return org;
	}

	
	/**
	 * 根据父组织ids获取子组织(通用)
	 * @return Org[]
	 */
	public Org[] getSub(String parentIds) {
		String[] ids=parentIds.split(",");
		List<Org> lists = new ArrayList<Org>();
		if(ids.length<=1000){
			List<Org> list = jdbcTemplate.query("SELECT * FROM TB_ORG WHERE PARENTID IN ("+parentIds+") AND STATE=1", new RowMapper<Org>(){
				
				@Override
				public Org mapRow(ResultSet rs, int arg1) throws SQLException {
					Org o = new Org();
					o.setId(rs.getLong("ID"));
					o.setParentId(rs.getLong("PARENTID"));
					o.setName(rs.getString("NAME"));
					o.setLevels(rs.getInt("LEVELS"));
					o.setAddress(rs.getString("ADDRESS"));
					o.setDescription(rs.getString("DESCRIPTION"));
					o.setOrgCode(rs.getString("ORGCODE"));
					return o;
				}
			});
			int size = list.size();
			Org[] orgs = new Org[size];
			for(int i = 0 ; i < size; i++) {
				orgs[i] = list.get(i);
			}
		return orgs;
		}else{
			int a = ids.length;
			System.out.println(a);
			int num = a%1000==0?a/1000:a/1000+1;
			System.out.println(num);
			for(int i=0;i<num;i++){
				StringBuffer sb = new StringBuffer();
				System.out.println(i);
				for(int j=i*1000;j<(i==num-1?a:(i+1)*1000);j++){
//					System.out.println(j);
//					System.out.println(ids[j]);
					sb.append(ids[j]).append(",");
				}
				sb.deleteCharAt(sb.length()-1);
				List<Org> list = jdbcTemplate.query("SELECT * FROM TB_ORG WHERE PARENTID IN ("+sb.toString()+") AND STATE=1", new RowMapper<Org>(){
					
					@Override
					public Org mapRow(ResultSet rs, int arg1) throws SQLException {
						Org o = new Org();
						o.setId(rs.getLong("ID"));
						o.setParentId(rs.getLong("PARENTID"));
						o.setName(rs.getString("NAME"));
						o.setLevels(rs.getInt("LEVELS"));
						o.setAddress(rs.getString("ADDRESS"));
						o.setDescription(rs.getString("DESCRIPTION"));
						o.setOrgCode(rs.getString("ORGCODE"));
						return o;
					}
				});
				lists.addAll(list);
			}
			Org[] orgs = new Org[lists.size()];
			for(int t=0;t<lists.size();t++){
				orgs[t] = lists.get(t);
			}
			return orgs;
		}
	}

/*	public Org[] getSub(String parentIds) {
		String sql = DB_SQL.ORG_QUERY_BY_PARENTID;
		String[] parentIdArr = parentIds.split(",");
		List<Org> orgLists = new ArrayList<Org>();
		for(String parentId : parentIdArr) {
			List<Org> lists = jdbcTemplate.query(sql, new Object[]{parentId}, mapper);
			for(Org org : lists) {
				orgLists.add(org);
			}
		}
		
		Org[] orgs = new Org[orgLists.size()];
		for(int i = 0 ; i < orgLists.size(); i++) {
			orgs[i] = orgLists.get(i);
		}
		return orgs;
	}
	
*/	
	/**
	 * 根据父组织ids获取子组织(组织管理)
	 * @return Org[]
	 */
	public Org[] getAllSub(String parentIds) {
		String[] ids = parentIds.split(",");
		List<Org> lists = new ArrayList<Org>();
		if(ids.length<=1000){
			System.out.println(parentIds+"     parentIds");
			List<Org> list = jdbcTemplate.query("SELECT * FROM TB_ORG WHERE PARENTID IN ("+parentIds+")", new RowMapper<Org>(){
				
				@Override
				public Org mapRow(ResultSet rs, int arg1) throws SQLException {
					Org o = new Org();
					o.setId(rs.getLong("ID"));
					o.setParentId(rs.getLong("PARENTID"));
					o.setName(rs.getString("NAME"));
					o.setLevels(rs.getInt("LEVELS"));
					o.setAddress(rs.getString("ADDRESS"));
					o.setDescription(rs.getString("DESCRIPTION"));
					o.setOrgCode(rs.getString("ORGCODE"));
					o.setState(rs.getInt("STATE"));
					return o;
				}
			});
			int size = list.size();
			Org[] orgs = new Org[size];
			for(int i = 0 ; i < size; i++) {
				orgs[i] = list.get(i);
			}
		return orgs;
		}else{
			int a = ids.length;
			System.out.println(a   +"aaaa");
			int num = a%1000==0?a/1000:a/1000+1;
			System.out.println(num);
			for(int i=0;i<num;i++){
				StringBuffer sb = new StringBuffer();
				System.out.println(i);
				for(int j=i*1000;j<(i==num-1?a:(i+1)*1000);j++){
//					System.out.println(j);
//					System.out.println(ids[j]);
					sb.append(ids[j]).append(",");
				}
				sb.deleteCharAt(sb.length()-1);
				List<Org> list = jdbcTemplate.query("SELECT * FROM TB_ORG WHERE PARENTID IN ("+sb.toString()+")", new RowMapper<Org>(){
					
					@Override
					public Org mapRow(ResultSet rs, int arg1) throws SQLException {
						Org o = new Org();
						o.setId(rs.getLong("ID"));
						o.setParentId(rs.getLong("PARENTID"));
						o.setName(rs.getString("NAME"));
						o.setLevels(rs.getInt("LEVELS"));
						o.setAddress(rs.getString("ADDRESS"));
						o.setDescription(rs.getString("DESCRIPTION"));
						o.setOrgCode(rs.getString("ORGCODE"));
						o.setState(rs.getInt("STATE"));
						return o;
					}
				});
				lists.addAll(list);
			}
			Org[] orgs = new Org[lists.size()];
			for(int t=0;t<lists.size();t++){
				orgs[t] = lists.get(t);
			}
			return orgs;
		}
	}
	/**
	 * 移动组织
	 */
	public GenRes move2(Long id, Long move2id) {
		GenRes genRes = new GenRes();
		String sql = DB_SQL.ORG_MOVE2;
		//组织表里变动
		try{
			int rows = jdbcTemplate.update(sql, move2id, id);
			//门店表里变动
			Org org = get(id);
			int level = org.getLevels();
			if(level==5){
				String update4 = DB_SQL.POINT_UPDATE4;
				long agency = get(move2id).getParentId();
				long filiale = get(agency).getParentId();
				jdbcTemplate.update(update4,filiale,agency,move2id,id);
			}else if(level==4){
				String pointMove3 = DB_SQL.POINT_QUERY_IDS3;
				List<Long> ids3 = jdbcTemplate.queryForList(pointMove3, new Object[]{id}, Long.class);
				String update3 = DB_SQL.POINT_UPDATE3;
				long filiale = get(move2id).getParentId();
				for(int i=0;i<ids3.size();i++){
					jdbcTemplate.update(update3,filiale,move2id,ids3.get(i));
				}
			}else if(level==3){
				String pointMove2 = DB_SQL.POINT_QUERY_IDS2;
				List<Long> ids2 = jdbcTemplate.queryForList(pointMove2, new Object[]{id}, Long.class);
				String update2 = DB_SQL.POINT_UPDATE2;
				for(int i=0;i<ids2.size();i++){
					jdbcTemplate.update(update2,move2id,ids2.get(i));
				}
			}
			genRes.setPint(rows);
			return genRes;		
		}catch (Exception e){
			e.printStackTrace();
			genRes.setExp(new Exp("系统异常！"));
			return genRes;
		}
	}

	/**
	 * 禁用/激活组织
	 */
	public GenRes forbidOrg(long id){
		String sqlq ="SELECT * FROM TB_ORG WHERE ID=? AND LEVELS=5";
		List<Org> orgs = jdbcTemplate.query(sqlq, new Object[]{id},mapper);
		String query = DB_SQL.ORG_QUERY_STATE_BY_ID;
		List<Integer> states = jdbcTemplate.queryForList(query, new Object[]{id}, Integer.class);
		String sql =DB_SQL.ORG_UPDATE_STATE;
		GenRes genRes = new GenRes();
		if(states.size()==0){
			genRes.setExp(new Exp("门店无禁用与否的标志"));
			return genRes;
		}
		if(states.get(0)==1){
			try{
				int i=0;
				int j = 0;
				if(orgs.size()==0||orgs==null){
					i = jdbcTemplate.update(sql, -1, id);
					genRes.setPint(i);
					return genRes;
				}else{
					i = jdbcTemplate.update(sql, -1, id);
					String qq = "UPDATE TB_POINT SET STATE=-1,CLOSETIME=SYSDATE WHERE ID=?";
					j = jdbcTemplate.update(qq,id);
					genRes.setPint(i+j);

					return genRes;
				}
				
			}catch(Exception e){
				e.printStackTrace();
				genRes.setExp(new Exp("系统异常！"));
				return genRes;
			}
		}else if(states.get(0)==-1){
			try{
				int i=0;
				int j = 0;
				if(orgs.size()==0||orgs==null){
					i = jdbcTemplate.update(sql, 1, id);
					genRes.setPint(i);
					return genRes;
				}else{
					i = jdbcTemplate.update(sql, 1, id);
					String qq = "UPDATE TB_POINT SET STATE= 1 , CLOSETIME=NULL WHERE ID=?";
					j = jdbcTemplate.update(qq,id);
					genRes.setPint(i+j);

					return genRes;
				}
				

			}catch(Exception e){
				e.printStackTrace();
				genRes.setExp(new Exp("系统异常！"));
				return genRes;
			}
		}else{
			genRes.setExp(new Exp("不能操作！"));
			return genRes;
		}
	}
	/**
	 * 获取所有
	 * @return
	 */
	public List<Org> getOrgs() {
		
		final Map<Long, Org> orgMap = new HashMap<Long, Org>();

		jdbcTemplate.query(DB_SQL.ORG_QUERY_ALL, new RowMapper<Org>(){
			
			@Override
			public Org mapRow(ResultSet rs, int arg1) throws SQLException {
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setParentId(rs.getLong("PARENTID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setAddress(rs.getString("ADDRESS"));
				o.setDescription(rs.getString("DESCRIPTION"));
				o.setOrgCode(rs.getString("ORGCODE"));

				if(o.getParentId()!=null && o.getParentId() > 0) {
					orgMap.get(o.getParentId()).addSub(o);
				}
				// FIXME 可能会有 key === equals 问题
				orgMap.put(o.getId(), o);
				return o;
			}
		});
		
		List<Org> orgs = new ArrayList<Org>();
		for(Long id : orgMap.keySet()) {
			orgs.add(orgMap.get(id));
		}
		
		return orgs;
	}
	
	/**
	 * 初始化map
	 */
	public void initOrgs() {
		
		final Map<Long, Org> tempMap = new HashMap<Long, Org>();

		jdbcTemplate.query(DB_SQL.ORG_QUERY_ALL, new RowMapper<Org>(){
			
			@Override
			public Org mapRow(ResultSet rs, int arg1) throws SQLException {
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setParentId(rs.getLong("PARENTID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setAddress(rs.getString("ADDRESS"));
				o.setDescription(rs.getString("DESCRIPTION"));
				o.setOrgCode(rs.getString("ORGCODE"));

				if(o.getParentId()!=null && o.getParentId() > 0) {
					tempMap.get(o.getParentId()).addSub(o);
				}
				tempMap.put(o.getId(), o);
				return o;
			}
		});
		
//		orgMap.clear();
//		orgMap = tempMap;
		
		System.out.println("Init Orgs: ......OK");
	}
	
	/**
	 * 根据levels查询组织
	 * @param levels
	 * @return
	 */
	public Org[] queryByLevels(int levels) {
		List<Org> lists = jdbcTemplate.query("SELECT ID, NAME,ORGCODE FROM TB_ORG WHERE LEVELS = ?", new RowMapper<Org>(){

			@Override
			public Org mapRow(ResultSet rs, int arg1) throws SQLException {
				Org org = new Org();
				org.setId(rs.getLong("ID"));
				org.setName(rs.getString("NAME"));
				org.setOrgCode(rs.getString("ORGCODE"));
				return org;
			}
			
		}, levels);
		Org[] orgs = new Org[lists.size()];
		for(int i = 0; i < lists.size(); i++) {
			orgs[i] = lists.get(i);
		}
		
		return orgs;
	}
	
	/**
	 * 分公司查办事处
	 */
	public Org[] getAllAgency(long id){
		String sql = DB_SQL.ORG_QUERY_AGENCY;
		List<Org> orgs = jdbcTemplate.query(sql, new Object[]{id},new RowMapper<Org>(){
			public Org mapRow(ResultSet rs, int index) throws SQLException{
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setOrgCode(rs.getString("ORGCODE"));
				return o;
			}
		});
		Org[] os = new Org[orgs.size()];
		for(int i=0;i<orgs.size();i++){
			os[i] = orgs.get(i);
		}
		return os;
	}
	/**
	 * 分公司查片区
	 */
	public Org[] getAllArea(long id){
		StringBuffer sb = new StringBuffer(DB_SQL.ORG_QUERY_AREA);
		Org[] os = getAllAgency(id);
		StringBuffer str = new StringBuffer();
		for(Org o:os){
			str.append(o.getId()).append(",");
		}
		str.deleteCharAt(str.length()-1);
		sb.append(" WHERE PARENTID IN ( ").append(str).append(" )");
		List<Org> orgs = jdbcTemplate.query(sb.toString(),new RowMapper<Org>(){
			public Org mapRow(ResultSet rs, int index) throws SQLException{
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setOrgCode(rs.getString("ORGCODE"));
				return o;
			}
		});
		Org[] os1 = new Org[orgs.size()];
		for(int i=0;i<orgs.size();i++){
			os1[i] = orgs.get(i);
		}
		return os1;
	}
	/**
	 * 分公司查门店
	 */
	public Org[] getAllPoint(long id){
		StringBuffer sb = new StringBuffer(DB_SQL.ORG_QUERY_AREA);
		Org[] os = getAllArea(id);
		StringBuffer str = new StringBuffer();
		for(Org o:os){
			str.append(o.getId()).append(",");
		}
		str.deleteCharAt(str.length()-1);
		sb.append(" WHERE PARENTID IN ( ").append(str).append(" )");
		List<Org> orgs = jdbcTemplate.query(sb.toString(),new RowMapper<Org>(){
			public Org mapRow(ResultSet rs, int index) throws SQLException{
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setOrgCode(rs.getString("ORGCODE"));
				return o;
			}
		});
		Org[] os1 = new Org[orgs.size()];
		for(int i=0;i<orgs.size();i++){
			os1[i] = orgs.get(i);
		}
		return os1;
	}
	/**
	 * 办事处查询门店
	 */
	public Org[] getAgencyPoint(long id) {
		String sql = "SELECT * FROM (SELECT * FROM TB_ORG START WITH ID = ? CONNECT BY PRIOR ID = PARENTID ORDER BY LEVELS) TOR WHERE TOR.LEVELS = 5";
		List<Org> orgs = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<Org>(){
			public Org mapRow(ResultSet rs, int index) throws SQLException{
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setOrgCode(rs.getString("ORGCODE"));
				return o;
			}
		});
		Org[] os1 = new Org[orgs.size()];
		for(int i=0;i<orgs.size();i++){
			os1[i] = orgs.get(i);
		}
		return os1;
	}
	
	/**
	 * 组织机构模糊查询
	 */
	public Org[] getOrgByName(Org org){
		int level = org.getLevels();
		String sql = "";
		List<Org> orgs = null;
		if(level==1){
			sql = "SELECT * FROM TB_ORG WHERE NAME LIKE '%"+org.getName()+"%' ORDER BY ID ";
			orgs=jdbcTemplate.query(sql,mapper);
		}else{
			sql = "SELECT * FROM (SELECT * FROM TB_ORG START WITH ID = ? CONNECT BY PRIOR ID = PARENTID ORDER BY ID) WHERE NAME LIKE '%"+org.getName()+"%' AND LEVELS > ?" ;
			orgs=jdbcTemplate.query(sql, new Object[]{org.getId(),org.getLevels()},mapper);
		}
		if(orgs.size()==0||null!=orgs){
			Org[] orgss = new Org[orgs.size()];
			for(int i=0;i<orgs.size();i++){
				orgss[i] = orgs.get(i);
			}
			return orgss;
		}else{
			return new Org[]{new Org()};
		}
	}
	
	/**
	 * 组织机构导入
	 * @param filePath
	 * @return
	 */
	public void initOrgs(String filePath) {
		List<List<String>> listRows = new ExcelUtil().read("c:/initOrgs.xls");
		//片区
		List<Object[]> paramList = new ArrayList<Object[]>();
		//分公司
		List<Object[]> fList = new ArrayList<Object[]>();
		//办事处
		List<Object[]> aList = new ArrayList<Object[]>();
//		List<Object[]> qList = new ArrayList<Object[]>();
		//分公司
		Map<String, String> fmap = new HashMap<String, String>();
		//办事处
		Map<String, String> amap = new HashMap<String, String>();
		
//		Set<String> qnames = new HashSet<String>();
		for(int i = 2; i < listRows.size(); i++) {
			List<String> row = listRows.get(i);
			Object[] objs = new Object[2];
			for(int j = 0; j < row.size(); j++) {
				if(j == 0) {
					fmap.put(row.get(1), row.get(0));
				}
				if(j == 2) {
					amap.put(row.get(3), row.get(2));
				}
				if(j == 4) {
					objs[0] = row.get(j + 1);
					objs[1] = row.get(j);
				}
//				qnames.add(row.get(8));
			}
			paramList.add(objs);
			
		}
		
		Set<String> fnames = fmap.keySet();
		for(String c : fnames) {
			fList.add(new Object[]{c, fmap.get(c)});
		}
		
		Set<String> anames = amap.keySet();
		for(String c : anames) {
			aList.add(new Object[]{c, amap.get(c)});
		}
		
//		for(String c : qnames) {
//			qList.add(new Object[]{c});
//		}
		//区域
//		jdbcTemplate.batchUpdate("INSERT INTO TB_ORG(ID, STATE, LEVELS, NAME) VALUES(SEQ_ORG.nextval, 1, 3, ?)", qList);
		//分公司
		int[] filiale = jdbcTemplate.batchUpdate("INSERT INTO TB_ORG(ID, LEVELS, STATE, NAME, ORGCODE) VALUES(SEQ_ORG.nextval, 1, 1, ?, ?)", fList);
		//办事处
		int[] agency = jdbcTemplate.batchUpdate("INSERT INTO TB_ORG(ID, LEVELS, STATE, NAME, ORGCODE) VALUES(SEQ_ORG.nextval, 2, 1, ?, ?)", aList);
		//片区
		int[] area = jdbcTemplate.batchUpdate("INSERT INTO TB_ORG(ID, LEVELS, STATE, NAME, ORGCODE) VALUES(SEQ_ORG.nextval, 3, 1, ?, ?)", paramList);
		
		Map<String, Long> orgMap = new HashMap<String, Long>();
		//initOrgs();
		
		for(Org o : getOrgs()) {
			orgMap.put(o.getName(), o.getId());
		}
		
		for(int i = 2; i < listRows.size(); i++) {
			List<String> row = listRows.get(i);
//			jdbcTemplate.update("UPDATE TB_ORG SET PARENTID = ? WHERE NAME = ? AND LEVELS = 3", orgMap.get(row.get(7)), row.get(8));
//			jdbcTemplate.update("UPDATE TB_ORG SET PARENTID = ? WHERE NAME = ? AND LEVELS = 4", orgMap.get(row.get(8)), row.get(1));
			jdbcTemplate.update("UPDATE TB_ORG SET PARENTID = ? WHERE NAME = ? AND LEVELS = 2", orgMap.get(row.get(1)), row.get(3));
			jdbcTemplate.update("UPDATE TB_ORG SET PARENTID = ? WHERE NAME = ? AND LEVELS = 3", orgMap.get(row.get(3)), row.get(5));
			
		}
		
		System.out.println("------->" + paramList.size());
		System.out.println("------->" + fmap.size());
		System.out.println("------->" + amap.size());
		System.out.println("------->" + area.length);
		System.out.println("------->" + agency.length);
		System.out.println("------->" + filiale.length);
//		return null;
	}

	
	/**
	 * 冰洗二期需求新增
	 * 根据所提供的组织信息，查询该
	 * 组织下所有门店信息
	 * @param id 组织id
	 * @return org[] 门店对象数组 ,未查询到返回空数组
	 */
	 public Org[] getPointByOrg(long id){
		//定义返回结果
		Org[] orgArray  = null;
		String sql = "select * from (SELECT * FROM TB_ORG k start with k.id = ?  CONNECT BY prior k.id = parentid ) m where m.levels=5";
		List<Org> orgs = jdbcTemplate.query(sql,new Object[]{id},new RowMapper<Org>(){
			public Org mapRow(ResultSet rs, int index) throws SQLException{
				Org o = new Org();
				o.setId(rs.getLong("ID"));
				o.setParentId(rs.getLong("PARENTID"));
				o.setName(rs.getString("NAME"));
				o.setLevels(rs.getInt("LEVELS"));
				o.setOrgCode(rs.getString("ORGCODE"));
				return o;
			}
		});
		if(orgs != null && orgs.size() > 0){
			orgArray = new Org[orgs.size()];
			for(int i=0;i<orgs.size();i++){
				orgArray[i] = orgs.get(i);
			}
		}
		return orgArray;
    }
	
	 
	/**
	 * 冰洗二期需求新增
	 * 根据登录的组织id及组织层级信息，获取其
	 * 关联子组织信息
	 * 1.总部查分公司
	 * 2.分公司查办事处
	 * 3.办事处查片区
	 * 4.片区查门店
	 * 5.门店自查
	 * @param id 组织id
	 * @param level 组织层级 
	 * 1.总部 2.分公司 3.办事处 4.片区  5.门店
	 * @return org[] 组织对象数组 ,未查询到返回空数组
	 */
	 public Org[] getSubOrgArrays(long id,int level){
	    	//定义变量
	    	Org[] osgArray = null;
	    	Object[] args = null;
	    	String sql = "select * from (SELECT * FROM TB_ORG k start with k.id = ?  CONNECT BY prior k.id = parentid ) m where m.levels=?";
	    	//总部登录,其它根据规则处理
	    	if(level == 1){
	    		args = new Object[]{id,4};
	    	}else if(level == 5){
	    		args = new Object[]{id,level};
	    	}else{
	    		args = new Object[]{id,level+1};
	    	}
	    	//获取组织信息
	        if(level > 0 && id > 0){
	        	List<Org> orgs = jdbcTemplate.query(sql.toString(),args,new RowMapper<Org>(){
	    			public Org mapRow(ResultSet rs, int index) throws SQLException{
	    				Org o = new Org();
	    				o.setId(rs.getLong("ID"));
	    				o.setParentId(rs.getLong("PARENTID"));
	    				o.setName(rs.getString("NAME"));
	    				o.setLevels(rs.getInt("LEVELS"));
	    				o.setOrgCode(rs.getString("ORGCODE"));
	    				return o;
	    			}
	    		});
	        	if(orgs != null && orgs.size() > 0){
	        		osgArray = new Org[orgs.size()];
	           		for(int i=0;i<orgs.size();i++){
	           			osgArray[i] = orgs.get(i);
	           		}
	        	}
	        }
	        return osgArray;
	    }
	 
	 
		/**
		 * 冰洗二期需求新增
		 * 根据登录的组织id及组织层级信息，获取其
		 * 关联子组织信息
		 * 1.总部查分公司
		 * 2.分公司查办事处
		 * 3.办事处查片区
		 * 4.片区查门店
		 * 5.门店自查
		 * @param id 组织id
		 * @param level 组织层级 
		 * 1.总部 2.分公司 3.办事处 4.片区  5.门店
		 * @return org[] 组织对象数组 ,未查询到返回空数组
		 */
		 public List<Org> getSubOrgArraysForPad(CommonBean commBean){
		    	//定义变量
		    	Org[] osgArray = null;
		    	Object[] args = null;
		    	List<Org> orgs = null;
		    	long id = 0;
		    	int level = 0;
                if(commBean == null){
			    	return orgs;
			    }
                id = commBean.getOrgId();
                level = commBean.getLevels();
                if(id == 0 || level == 0){
                	return orgs;
                }
		    	String sql = "select * from (SELECT * FROM TB_ORG k where k.state=1 start with k.id = ?  CONNECT BY prior k.id = parentid ) m where m.levels=?";
		    	//总部登录,其它根据规则处理
		    	if(level == 1){
		    		args = new Object[]{id,4};
		    	}else if(level == 5){
		    		args = new Object[]{id,level};
		    	}else{
		    		args = new Object[]{id,level+1};
		    	}
		    	//获取组织信息
		        if(level > 0 && id > 0){
		        	   orgs = jdbcTemplate.query(sql.toString(),args,new RowMapper<Org>(){
		    			public Org mapRow(ResultSet rs, int index) throws SQLException{
		    				Org o = new Org();
		    				o.setId(rs.getLong("ID"));
		    				o.setParentId(rs.getLong("PARENTID"));
		    				o.setName(rs.getString("NAME"));
		    				o.setLevels(rs.getInt("LEVELS"));
		    				o.setOrgCode(rs.getString("ORGCODE"));
		    				return o;
		    			}
		    		});
		        }
		        return orgs;
	 }
		 
    /**
	 * 冰洗二期需求新增
	 * 根据登录的组织id及组织层级信息，获取其
	 * 关联子组织信息
	 * 1.总部查分公司
	 * 2.分公司查办事处
	 * 3.办事处查片区
	 * 4.片区查门店
	 * 5.门店自查
	 * @param id 登录人组织id
	 * @param level 组织层级 
	 * 1.总部 2.分公司 3.办事处 4.片区  5.门店
	 * @return ong[] 门店对象id数组 ,未查询到返回空数组
	 */
    public Long[] getPointsByOrgs(long id,int level){
    	//定义变量
    	Long[] returnOrgArray = null;
    	List<Long> orgsList = new ArrayList<Long>();
    	if(id != 0 &&  level != 0){
    		Org[] subOrgArrays = getSubOrgArrays(id,level);
    		if(subOrgArrays != null){
    			for (Org org : subOrgArrays){
    				Org[] pointArray = getPointByOrg(org.getId());
    				if(pointArray != null && pointArray.length > 0){
    					for(int i = 0 ; i < pointArray.length ; i++){
    						Org tempOrg = pointArray[i];
    						orgsList.add(tempOrg.getId());
    					}
    				}
    			}
    			if(orgsList.size() > 0){
    				returnOrgArray = new Long[orgsList.size()];
    				for(int i = 0 ; i < orgsList.size() ; i++){
        				long pointid = orgsList.get(i);
        				returnOrgArray[i]  = pointid;
        			}
    			}
    		}
    	}
    	return returnOrgArray;
    }
    
    /**
	 * 冰洗二期需求新增
	 * 根据提供的组织id数组，获取其
	 * 组织下的所有门店id数组集合
	 * @param Long[] ids 待获取门店的父级组织ID
	 * 1.总部 2.分公司 3.办事处 4.片区  5.门店
	 * @return Long[] 门店对象id数组 ,未查询到返回空数组
	 */
    public Long[] getPointsByOrgs(Long[] ids){
    	//定义变量
    	Long[] returnOrgArray = null;
    	List<Long> orgsList = new ArrayList<Long>();
    	if(ids != null && ids.length >0 ){
    			for (int t = 0 ; t < ids.length ;t++){
    				Org[] pointArray = getPointByOrg(ids[t]);
    				if(pointArray != null && pointArray.length > 0){
    					for(int i = 0 ; i < pointArray.length ; i++){
    						Org tempOrg = pointArray[i];
    						orgsList.add(tempOrg.getId());
    					}
    				}
    			}
    			if(orgsList.size() > 0){
    				returnOrgArray = new Long[orgsList.size()];
    				for(int i = 0 ; i < orgsList.size() ; i++){
        				long pointid = orgsList.get(i);
        				returnOrgArray[i]  = pointid;
        			}
    			}
    	}
    	return returnOrgArray;
    }

    /**
	 * 冰洗二期需求新增(辅助)
	 * 根据登录的组织id及组织层级信息，获取其
	 * 父级别组织名称
	 * 1.总部查分公司
	 * 2.分公司查办事处
	 * 3.办事处查片区
	 * 4.片区查门店
	 * 5.门店自查
	 * @param id 登录人组织id
	 * @param level 组织层级 
	 * 1.总部 2.分公司 3.办事处 4.片区  5.门店
	 * @return String 父级别组织名称
	 */
    public String getCurOrgParentName(long id,int level){
    	//定义变量
    	List<Long> orgsList = new ArrayList<Long>();
    	String resultStr = "";
    	if(id == 0 || level ==0){
    		return resultStr;
    	}
    	String sql = "select name from (SELECT * FROM TB_ORG k start with k.id = ?  CONNECT BY prior parentid =k.id ) m where m.levels=?";
    	if(id != 0 &&  level != 0){
    		List<String> resutl = jdbcTemplate.queryForList(sql, String.class, new Object[]{id,level});
    		if(resutl != null && resutl.size() > 0){
    			resultStr  = resutl.get(0);
    		}
    	}
    	return resultStr;
    }
}
